/**   
 * Copyright © 2019 eSunny Info. Tech Ltd. All rights reserved.
 * 
 * @Description:TODO(功能)
 * @className ProductsServiceImpl.java
 * @Package: com.erp.service 
 * @author: yj   
 * @date: 2019年1月17日 上午9:56:51 
 */
package com.erp.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.erp.dao.ProductsDaoImpl;
import com.erp.po.Categorys;
import com.erp.po.Products;
import com.erp.po.Providers;
import com.erp.util.DBHelper;
import com.erp.util.Page;

/**
 * @className ProductsServiceImpl.java
 * @author yj
 * @Description:TODO(功能)
 * @Date 2019年1月17日
 */
public class ProductsServiceImpl implements ProductsService {

	private ProductsDaoImpl pd;
	
	public ProductsServiceImpl() {
		pd = new ProductsDaoImpl();
	}
	
	/* (查询 ( 查询 Products <商品> 全部 )(分页))
	 * 
	 */
	@Override
	public Page<Products> findProductsListByPage(Page<Products> page) {
		Connection conn = null;
		ResultSet set = null;
		int total = 0;
		//Page<Products> pages = new Page<Products>();
		ArrayList<Products> list = new ArrayList<Products>();
		
		try {
			conn = DBHelper.getConnection();

			//查询总记录数
			ResultSet set2 = pd.selectProductsByTotal(conn);
			while (set2.next()) {
				total = set2.getInt(1);
			}
			
			// 查询数据
			set = pd.selectProductsByPage(conn, page);
			while (set.next()) {
				int productId = set.getInt("productID");
				String productName = set.getString("product_name");
				double incomePrice = set.getDouble("income_price");
				int quantity = set.getInt("quantity");
				double salesPrice = set.getDouble("sales_price");
				Date incomeTime = set.getDate("income_time");
				// 类别
				String categoryName = set.getString("category_name");
				// 供应商
				//int providerId = set.getInt("providerID");
				String providerName = set.getString("provider_name");

				list.add(new Products(productId, productName, incomePrice, quantity, salesPrice, incomeTime,
						new Providers(providerName), new Categorys(categoryName)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pd.close();
		}
		page.setList(list);
		page.setTotal(total);
		
		return page;
	}

	/* (增加 ( 增加 商品 )) 查询 (是否有添加重名的商品)
	 * 
	 */
	@Override
	public int addProducts(Products products) {
		Connection conn = null;
		//boolean f = false;
		int code = 0;  // 300: 有空串     |  301: 重复值       | 302: 成功       |  303: 商品类别不存在      | 304: 供应商不存在
		
		if (products == null || products.getProductName().equals("") || products.getProductName().equals(" ")
				|| products.getIncomePrice() <= 0 || products.getQuantity() == 0 || products.getSalesPrice() <= 0 
				|| products.getCategorys() == null || products.getProviders() == null) {
			code = 300;
			return code;
		}

		try {
			conn = DBHelper.getConnection();
			
			// 查询 (商品类别id是否有存在)
			ResultSet setCate = pd.selectProductsByCateId(conn, products);
			if (setCate.next()) {
				int cate = setCate.getInt(1);
				if (cate == 0) {
					code = 303;
					return code;
				}
			}
			
			// 查询 (供应商id是否有存在)
			ResultSet setPro = pd.selectProductsByProId(conn, products);
			if (setPro.next()) {
				int pro = setPro.getInt(1);
				if (pro == 0) {
					code = 304;
					return code;
				}
			}
			
			
			// 查询 (是否有添加重名的商品)
			ResultSet set = pd.selectProductsByInsert(conn, products);
			if (set.next()) {
				// return false 添加失败
				//f = false;
				code = 301;
			} else {
				// 事务处理
				conn.setAutoCommit(false);
				int i = pd.insertProducts(conn, products);
				conn.commit();
				conn.setAutoCommit(true);
				if (i > 0) {
					//f = true;
					code = 302;
				} else {
					//f = false;
					code = 301;
				}
			}
			
		} catch (SQLException e) {
			
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			pd.close();
		}
		
		return code;
	}

	/* ( 修改 ( 修改商品属性: 商品名称 , 进价 , 售价 , 存货(数量) ) / 针对本 id 的名称不修改,不构成重名  )
	 * 
	 */
	@Override
	public int modifyProductsById(Products products) {
		Connection conn = null;
		//boolean f = false;
		int code = 0; // 300: 有空串     |  301: 重复值       | 302: 成功     |  303: 没有该类别
		
		if (products == null || products.getProductName().equals("") || products.getProductName().equals(" ")
				|| products.getIncomePrice() <= 0 || products.getSalesPrice() <= 0 || products.getQuantity() == 0) {
			code = 300;
			return code;
		}
		
		try {
			conn = DBHelper.getConnection();
			
			// 查询 (针对本 id 的名称不修改,不构成重名)
			ResultSet set = pd.selectProductsByInsert(conn, products);
			if (set.next()) {
				// return false 添加失败
				// f = false;
				int findId = set.getInt(1);
				if (!(products.getProductId() == findId)) {
					code = 301;
					return code;
				}
			}

			// 事务处理
			conn.setAutoCommit(false);
			int i = pd.updateProductsById(conn, products);
			conn.commit();
			conn.setAutoCommit(true);
			if (i > 0) {
				// f = true;
				code = 302;
			} else {
				// f = false;
				code = 303;
			}

		} catch (SQLException e) {
			
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			pd.close();
		}

		return code;
	}

	/* (删除 ( 根据id 删除商品 ))
	 * 
	 */
	@Override
	public boolean removeProductsById(int productId) {
		Connection conn = null;
		boolean f = false;

		// 查询 (是否有该商品)
//		ResultSet set = pd.selectProductsByDelete(conn, productId);
//		if (set.next()) {
//			
//		}
		
		try {
			conn = DBHelper.getConnection();
			
			//事务处理
			conn.setAutoCommit(false);
			int i = pd.deleteProductsById(conn, productId);
			conn.commit();
			conn.setAutoCommit(true);
			if (i > 0) {
				f = true;
			} else {
				f = false;
			}
			
		} catch (SQLException e) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			pd.close();
		}
		
		return f;
	}

	/* (根据 id 查询product)
	 * 
	 */
	@Override
	public Products findProductById(int id) {
		Connection conn = null;
		ResultSet set = null;
		try {
			conn = DBHelper.getConnection();
			set = pd.selectProductById(conn, id);
			if (set.next()) {
				int productId = set.getInt("productID");
				String productName = set.getString("product_name");
				double incomePrice = set.getDouble("income_price");
				int quantity = set.getInt("quantity");
				double salesPrice = set.getDouble("sales_price");
				Date incomeTime = set.getDate("income_time");
				// 类别
				int categoryId = set.getInt("categoryID");
				// 供应商
				int providerId = set.getInt("providerID");

				return new Products(productId, productName, incomePrice, quantity, salesPrice, incomeTime,
						new Providers(providerId), new Categorys(categoryId));
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			pd.close();
		}
		
		return null;
	}

	/**
	 * 校验product重名
	 */
	public boolean findProductByName(String productName) {
		Connection conn = null;
		ResultSet set = null;
		try {
			conn = DBHelper.getConnection();
			
			set = pd.selectProductByName(conn, productName);
			if(set.next()) {
				return false;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pd.close();
		}
		return true;
	}

	/* 
	 * 根据名称 查询product(本id修改 不构成重名)
	 */
	@Override
	public Products findProductByPName(String productName) {
		Connection conn = null;
		ResultSet set = null;
		try {
			conn = DBHelper.getConnection();
			set = pd.selectProductByName(conn, productName);
			if(set.next()) {
				int productId = set.getInt("productID");

				return new Products(productId);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			pd.close();
		}
		return null;
	}

	/* 
	 * 根据条件查询 ( 根据条件查询 Products <商品> 全部 )(分页)
	 */
	@Override
	public Page<Products> findProductsListByCondition(Products p, Page<Products> page) {
		Connection conn = null;
		ResultSet set = null;
		int total = 0;
		
		ArrayList<Products> list = new ArrayList<Products>();
		
		try {
			conn = DBHelper.getConnection();

			//查询总记录数
			ResultSet set2 = pd.selectConditionByTotal(conn, p);
			if (set2.next()) {
				total = set2.getInt(1);
			}
			
			// 查询数据
			set = pd.selectProductsByCondition(conn, p, page);
			while (set.next()) {
				int productId = set.getInt("productID");
				String productName = set.getString("product_name");
				double incomePrice = set.getDouble("income_price");
				int quantity = set.getInt("quantity");
				double salesPrice = set.getDouble("sales_price");
				Date incomeTime = set.getDate("income_time");
				// 类别
				String categoryName = set.getString("category_name");
				// 供应商
				//int providerId = set.getInt("providerID");
				String providerName = set.getString("provider_name");

				list.add(new Products(productId, productName, incomePrice, quantity, salesPrice, incomeTime,
						new Providers(providerName), new Categorys(categoryName)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pd.close();
		}
		page.setList(list);
		page.setTotal(total);
		
		return page;
	}

}
